package com.fuxi.cloud.dyndb.util;

import cn.hutool.core.util.ObjectUtil;
import com.fuxi.cloud.dyndb.JmreportDataSourceCachePool;
import com.fuxi.cloud.dyndb.vo.JmreportDynamicDataSourceVo;

import java.text.MessageFormat;
import java.util.Iterator;
import java.util.Map;

/**
 * JmreportSqlUtils
 *
 * @Author: 扫地僧
 * @CreateTime: 2021-05-03
 */

public class JmreportSqlUtils {
    public static final String DATABSE_TYPE_MYSQL = "mysql";
    public static final String DATABSE_TYPE_POSTGRE = "postgresql";
    public static final String DATABSE_TYPE_ORACLE = "oracle";
    public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";
    public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";
    public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";
    public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";
    public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";
    public static final String MYSQL_ALLTABLES_SQL = "select distinct table_name from information_schema.columns where table_schema = {0}";
    public static final String POSTGRE__ALLTABLES_SQL = "SELECT distinct c.relname AS  table_name FROM pg_class c";
    public static final String ORACLE__ALLTABLES_SQL = "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
    public static final String SQLSERVER__ALLTABLES_SQL = "select distinct c.name as  table_name from sys.objects c";
    public static final String MYSQL_ALLCOLUMNS_SQL = "select column_name from information_schema.columns where table_name = {0} and table_schema = {1}";
    public static final String POSTGRE_ALLCOLUMNS_SQL = "select column_name from information_schema.columns  where table_name={0}";
    public static final String ORACLE_ALLCOLUMNS_SQL = "select column_name from all_tab_columns where table_name ={0}";
    public static final String SQLSERVER_ALLCOLUMNS_SQL = "select name from syscolumns where id={0}";
    public static final String MYSQL_ALLTABLES_COMMENT_SQL = "show table status";
    public static final String POSTGRE_ALLTABLES_COMMENT_SQL = "select relname as name,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c  where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname";
    public static final String ORACLE_ALLTABLES_COMMENT_SQL = "select table_name Name,comments Comments from user_tab_comments";
    public static final String SQLSERVER_ALLTABLES_COMMENT_SQL = "SELECT DISTINCT CAST(d.name AS varchar(500)) Name,CAST(f.value AS varchar(500)) Comment FROM syscolumns a\nLEFT JOIN systypes b ON a.xusertype= b.xusertype\nINNER JOIN sysobjects d ON a.id= d.id\nAND d.xtype= 'U'\nAND d.name<> 'dtproperties'\nLEFT JOIN syscomments e ON a.cdefault= e.id\nLEFT JOIN sys.extended_properties g ON a.id= G.major_id\nAND a.colid= g.minor_id\nLEFT JOIN sys.extended_properties f ON d.id= f.major_id\nAND f.minor_id= 0 ;";

    public JmreportSqlUtils() {
    }

    public static boolean dbTypeIsMySQL(String dbType) {
        return dbTypeIf(dbType, "mysql", "1");
    }

    public static boolean dbTypeIsOracle(String dbType) {
        return dbTypeIf(dbType, "oracle", "2");
    }

    public static boolean dbTypeIsSQLServer(String dbType) {
        return dbTypeIf(dbType, "sqlserver", "3");
    }

    public static boolean dbTypeIsPostgre(String dbType) {
        return dbTypeIf(dbType, "postgresql", "4");
    }

    public static boolean dbTypeIf(String dbType, String... correctTypes) {
        String[] var2 = correctTypes;
        int var3 = correctTypes.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            String var5 = var2[var4];
            if (dbType.toLowerCase().indexOf(var5.toLowerCase()) >= 0) {
                return true;
            }
        }

        return false;
    }

    public static String getFullSql(String sql, Map params) {
        return getFullSql(sql, params, (String)null, (String)null);
    }

    public static String getFullSql(String sql, Map params, String orderColumn, String orderBy) {
        StringBuilder var4 = new StringBuilder();
        var4.append("SELECT t.* FROM ( ").append(sql).append(" ) t ");
        if (params != null && params.size() >= 1) {
            var4.append("WHERE 1=1 ");
            Iterator var5 = params.keySet().iterator();

            while(var5.hasNext()) {
                Object var6 = var5.next();
                String var7 = String.valueOf(params.get(var6));
                if (ObjectUtil.isNotEmpty(var7)) {
                    var4.append(" AND (").append(var6).append(" = N'").append(var7).append("')");
                }
            }

            if (ObjectUtil.isNotEmpty(orderColumn) && ObjectUtil.isNotEmpty(orderBy)) {
                var4.append("ORDER BY ").append(orderColumn).append(" ").append("DESC".equalsIgnoreCase(orderBy) ? "DESC" : "ASC");
            }
        }

        return var4.toString();
    }

    public static String getCountSql(String sql) {
        return String.format("SELECT COUNT(1) total FROM ( %s ) temp_count", sql);
    }

    public static String createPageSqlByDBType(String dbType, String sql, int page, int rows) {
        int var4 = (page - 1) * rows;
        Object[] var5 = new Object[]{sql, String.valueOf(var4), String.valueOf(rows)};
        if (dbTypeIsMySQL(dbType)) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {1},{2}", var5);
        } else if (dbTypeIsPostgre(dbType)) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", var5);
        } else {
            int var6 = (page - 1) * rows;
            int var7 = var6 + rows;
            var5[2] = Integer.toString(var6);
            var5[1] = Integer.toString(var7);
            if (dbTypeIsOracle(dbType)) {
                sql = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", var5);
            } else if (dbTypeIsSQLServer(dbType)) {
                var5[0] = sql.substring(getAfterSelectInsertPoint(sql));
                sql = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", var5);
            }
        }

        return sql;
    }

    public static String createPageSqlByDBKey(String dbKey, String sql, int page, int rows) {
        JmreportDynamicDataSourceVo var4 = JmreportDataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
        String var5 = var4.getDbType();
        return createPageSqlByDBType(var5, sql, page, rows);
    }

    private static int getAfterSelectInsertPoint(String sql) {
        int var1 = sql.toLowerCase().indexOf("select");
        int var2 = sql.toLowerCase().indexOf("select distinct");
        return var1 + (var2 == var1 ? 15 : 6);
    }

    public static String getAllTableSql(String dbType, Object... params) {
        if (ObjectUtil.isNotEmpty(dbType)) {
            if (dbTypeIsMySQL(dbType)) {
                return MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", params);
            }

            if (dbTypeIsOracle(dbType)) {
                return "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
            }

            if (dbTypeIsPostgre(dbType)) {
                return "SELECT distinct c.relname AS  table_name FROM pg_class c";
            }

            if (dbTypeIsSQLServer(dbType)) {
                return "select distinct c.name as  table_name from sys.objects c";
            }
        }

        return null;
    }

    public static String getAllTableSql(String dbType) {
        if (ObjectUtil.isNotEmpty(dbType)) {
            if (dbTypeIsMySQL(dbType)) {
                return "show table status";
            }

            if (dbTypeIsOracle(dbType)) {
                return "select table_name Name,comments Comments from user_tab_comments";
            }

            if (dbTypeIsPostgre(dbType)) {
                return "select relname as name,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c  where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname";
            }

            if (dbTypeIsSQLServer(dbType)) {
                return "SELECT DISTINCT CAST(d.name AS varchar(500)) Name,CAST(f.value AS varchar(500)) Comment FROM syscolumns a\nLEFT JOIN systypes b ON a.xusertype= b.xusertype\nINNER JOIN sysobjects d ON a.id= d.id\nAND d.xtype= 'U'\nAND d.name<> 'dtproperties'\nLEFT JOIN syscomments e ON a.cdefault= e.id\nLEFT JOIN sys.extended_properties g ON a.id= G.major_id\nAND a.colid= g.minor_id\nLEFT JOIN sys.extended_properties f ON d.id= f.major_id\nAND f.minor_id= 0 ;";
            }
        }

        return null;
    }

    public static String getAllColumnSQL(String dbType, Object... params) {
        if (ObjectUtil.isNotEmpty(dbType)) {
            if (dbTypeIsMySQL(dbType)) {
                return MessageFormat.format("select column_name from information_schema.columns where table_name = {0} and table_schema = {1}", params);
            }

            if (dbTypeIsOracle(dbType)) {
                return MessageFormat.format("select column_name from all_tab_columns where table_name ={0}", params);
            }

            if (dbTypeIsPostgre(dbType)) {
                return MessageFormat.format("select column_name from information_schema.columns  where table_name={0}", params);
            }

            if (dbTypeIsSQLServer(dbType)) {
                return MessageFormat.format("select name from syscolumns where id={0}", params);
            }
        }

        return null;
    }
}

